
clear
**load data with baseline charteristics and data needed to define other occupational groups
odbc load, exec("select Kommun, occ_code=Ssyk4 ,sector_code07 = AstSNI2007,  Sun2000niva, Sun2000Inr, exam_year=ExamAr, SektorKod, wage_inc=LoneInk, lab_earnings=ForvErs, o_nr=Peorgnr_LopNr,   p_id=Person_LopNr from P0624_Lisa_2011") dsn("P0624") clear

*drop _merge
	
*add data on birthyear and sex
cd "\\micro.intra\Projekt\P0624$\P0624_Gem\Data Extraction and Files\Data files"
joinby p_id using "Birthyear_sex.dta" , unmatched(master)
	
gen year=2011

sort p_id year
destring occ_code sector Sun2000niva, force replace

cd  "\\micro.intra\Projekt\P0624$\P0624_Gem\Data Extraction and Files\Figures population comparisons\firms"
drop _merge

joinby o_nr year using "firms 20 emp 2001_2012.dta" , unmatched(master)
gen electionperiod=2010
gen valar=2010

*Add politician data
joinby electionperiod p_id using "\\micro.intra\Projekt\P0624$\P0624_Gem\Data Extraction and Files\Data files\pol_grund.dta", unmatched(master) _merge(_merge2)
*add data on politicial appointments
joinby valar p_id using "\\micro.intra\Projekt\P0624$\P0624_Gem\Data Extraction and Files\Data files\uppdrag.dta", unmatched(master) _merge(_merge3)
*add data wtih draft data
joinby p_id using "\\micro.intra\Projekt\P0624$\P0624_Gem\Data Extraction and Files\Data files\enlistment base file.dta", unmatched(master) _merge(_merge4)
*add earnings score
joinby p_id using "\\micro.intra\Projekt\P0624$\P0624_Gem\Data Extraction and Files\Data files\income residual new.dta", unmatched(master) _merge(_merge5)
*add data on election to parliament
joinby electionperiod p_id using "\\micro.intra\Projekt\P0624$\P0624_Gem\Data Extraction and Files\Data files\elected parl 82_10.dta", unmatched(master) _merge(_merge6)
gen ceo =occ_code==1210


gen electionyear=valar

*add data on nominations to parltiament
joinby electionyear p_id using "\\micro.intra\Projekt\P0624$\P0624_Gem\Data Extraction and Files\Data files\nom_parl_91_10.dta", unmatched(master)
replace Sun2000Inr="" if Sun2000Inr=="721x"

*add data on doctors
gen inr=substr(Sun2000Inr, 1,3)
gen doctor_exam=inr=="721"

*clean draft data
replace pprf_befl = . if pprf_befl==0
ren pprf_befl Leadership
replace pprf_pgrp = . if pprf_pgrp==0
ren pprf_pgrp Cognitive

*calcuilate years of education based on education categories		
replace  Sun2000niva=. if Sun2000niva>=999

gen educ_year=.
replace educ_year= 7.5 if Sun2000niva>=100 & Sun2000niva<200
replace educ_year= 9.4 if Sun2000niva>=200 & Sun2000niva<300
replace educ_year= 11.2 if Sun2000niva>=300 & Sun2000niva<330
replace educ_year= 12.4 if Sun2000niva>=330 & Sun2000niva<400
replace educ_year= 14.2 if Sun2000niva>=410& Sun2000niva<530
replace educ_year= 17 if Sun2000niva>=530 & Sun2000niva<600
replace educ_year= 20.4 if Sun2000niva>=600 & Sun2000niva<=1000

*define those with econ and polici phds
gen phd_econ = Sun2000niva==640 & inr=="314"
gen phd_pol =Sun2000niva==640 & Sun2000niva<=1000 & inr=="313"

*define dummy for working in the university
gen uni=sector==85420
*define dummy for working as lawyer
gen law= occ_code==2421| occ_code==2422

*change earnings variable so that it is measured in 1000's of Sek
replace lab_earnings = lab_earnings/10

*omit all those that have reached reritrement age
keep if FodelseAr >1949 & FodelseAr !=.

*data for table 1
log using comparison, replace
sum Leadership Cognitive inc_res educ_year lab if FodelseAr >1949 & FodelseAr !=. & nom==1&  vald!=1 &kso!=1
sum Leadership Cognitive inc_res educ_year lab if FodelseAr >1949 & FodelseAr !=. & vald==1 &kso!=1
sum Leadership Cognitive inc_res educ_year lab if FodelseAr >1949 & FodelseAr !=. &kso==1
sum Leadership Cognitive inc_res educ_year lab if FodelseAr >1949 & FodelseAr !=. &elec_parl==1
sum Leadership Cognitive inc_res educ_year lab if FodelseAr >1949 & FodelseAr !=. & ceo==1 & empl>=10 & empl<25
sum Leadership Cognitive inc_res educ_year lab if FodelseAr >1949 & FodelseAr !=. & ceo==1 & empl>=25 & empl<250
sum Leadership Cognitive inc_res educ_year lab if FodelseAr >1949 & FodelseAr !=. & ceo==1 & empl>=250 & empl<500000000
sum Leadership Cognitive inc_res educ_year lab if FodelseAr >1949 & FodelseAr !=. & law==1
sum Leadership Cognitive inc_res educ_year lab if FodelseAr >1949 & FodelseAr !=. & doctor_exam==1
sum Leadership Cognitive inc_res educ_year lab if FodelseAr >1949 & FodelseAr !=. & phd_econ==1 & uni==1
sum Leadership Cognitive inc_res educ_year lab if FodelseAr >1949 & FodelseAr !=. & phd_pol==1 & uni==1

log close


